﻿<cfsilent>
	<cfscript>

		datetimeAdvice = getProperty("serviceFactory").getBean("datetimeAdvice");
	
		sql = "SELECT 
					b.stu_id,b.stu_name,
					case when e.institute_name is null then '-' else e.institute_name end institute_name,
					case when d.sbj_name is null then '-' else d.sbj_name end sbj_name, 
					case when c.grade is null then '-' else c.grade end grade, 
					case when c.cls_name is null then '-' else c.cls_name end cls_name,
					f.sbj_name sbj_name1,f.sbj_id sbj_id1,
					a.sbj_direction,a.grade,
					a.reviewed
				FROM 
					t_student_secdegree a  
					INNER JOIN t_student b ON b.stu_id = a.stu_id
					INNER JOIN t_class c ON c.cls_id = b.cls_id 
					INNER JOIN t_subject d ON d.sbj_id = c.sbj_id 
					INNER JOIN t_institute e ON e.institute_id = d.institute_id 
					INNER JOIN t_subject f ON f.sbj_id = a.sbj_id 
				WHERE 
					a.stat = '1'
					ORDER BY a.reviewed";
					
		queryObj = new query( datasource=application.dnsSlave);
		
		rs_history = queryObj.execute( sql=sql ).getResult();
		
		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat( "本校学生双学位学生名单", "utf-8");
		
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew( "本校学生双学位学生名单", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "学号,姓名,学院,专业,年级,班级,双学位专业,双学位专业方向,双学位年级,审批日期");
		
		row = 1;
		
		for (a=1; a LTE rs_history.recordCount; a++ ) {
			
			row++;
			subjectDirectionID = rs_history["sbj_id1"][a]&rs_history["sbj_direction"][a];
			
			sql = "SELECT a.sbj_name 
					FROM t_subject a 
					WHERE a.sbj_id = :sbjId ";
                                                
            queryObj = new query( datasource=application.dnsSlave);
            queryObj.addParam( name="sbjId", value=subjectDirectionID, cfsqltype="cf_sql_varchar" );
                                
            rs_subjectDir= queryObj.execute( sql=sql ).getResult();
			
			if(rs_subjectDir.recordCount EQ 0){
				subjectDirectionName="无";
			}else{
				subjectDirectionName=rs_subjectDir.sbj_name;
			}
			/* 构建双学位学生信息 */
			rowData = rs_history["stu_id"][a]
						& "," & rs_history["stu_name"][a]
						& "," & rs_history["institute_name"][a]
						& "," & rs_history["sbj_name"][a]
						& "," & rs_history["grade"][a]
						& "," & rs_history["cls_name"][a]
						& "," & rs_history["sbj_name1"][a]
						& "," & subjectDirectionName
						& "," & rs_history["grade"][a]
						& "," & datetimeAdvice.formatDateString(rs_history["reviewed"][a], 'YYYY/MM/DD');
						
			/* 写入双学位学生信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);

		}
		
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);

	</cfscript>

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />

</cfsilent>